UNION
Overview
Use UNION keyword to combine several queries together. As a result, you get a dataset with fields returned by all included in the UNION queries chronologically arranged by their timestamps. UNION can be applied to querying from the same or different streams.
Polymorphic Dataset
You get a polymorphic dataset as a result of a UNION in case it combines messages of different classes.
#UNION of these two queries will return a polymorphic dataset with fields of both classes TradeMessage and BestBidOfferMessage
SELECT
trade.price AS "price",
trade.size AS "size"
TYPE "TradeMessage"
FROM kraken
ARRAY JOIN entries[THIS IS KrakenTradeEntry] AS trade
UNION
SELECT
bbo[side == ASK].price AS "offerPrice",
bbo[side == ASK].size AS "offerSize",
bbo[side == BID].price AS "bidPrice",
bbo[side == BID].size AS "bidSize"
TYPE "BestBidOfferMessage"
FROM kraken
ARRAY JOIN (entries AS array(L1entry))[THIS IS NOT NULL] AS bbo
#UNION of these two queries will return a polymorphic dataset with fields of both classes Bar1min and Bar5min
WITH entries[THIS IS TradeEntry] AS 'entries'
SELECT
sum{}(sum(entries.size)) AS 'volume',
first{}(entries[0].price) AS 'open',
last{}(entries[-1].price) AS 'close',
max{}(max(entries.price)) AS 'high',
min{}(min(entries.price)) AS 'low'
TYPE "Bar1min"
FROM bitfinex
OVER TIME(1m)
WHERE symbol == 'BTCUSD'
AND notEmpty(entries)
UNION
WITH entries[THIS IS TradeEntry] AS 'entries'
SELECT
sum{}(sum(entries.size)) AS 'volume',
first{}(entries[0].price) AS 'open',
last{}(entries[-1].price) AS 'close',
max{}(max(entries.price)) AS 'high',
min{}(min(entries.price)) AS 'low'
TYPE "Bar5min"
FROM bitfinex
OVER TIME(5m)
WHERE symbol == 'BTCUSD'
AND notEmpty(entries)
tip
You can also use RECORD ... TYPE ... WHEN construction to produce a polymorphic data set in a single query.
Fixed-Type Dataset
In case UNION combines messages of the same class, you get a fixed type dataset with fields from all the involved queries merged in one class. In case of identical column names, UNION places results in one column chronologically.
tip
It is not possible to UNION two fields with the same Name but different data types. For example, field Price of integer and string data types cannot be combined by UNION in one field.
#UNION of these two queries will return a fixed type dataset with fields from both queries' results combined in one TradeAndBBO class.
SELECT
trade.price AS "price",
trade.size AS "size"
TYPE "TradeAndBBO"
FROM kraken
ARRAY JOIN entries[THIS IS KrakenTradeEntry] AS trade
UNION
SELECT
bbo[side == ASK].price AS "offerPrice",
bbo[side == ASK].size AS "offerSize",
bbo[side == BID].price AS "bidPrice",
bbo[side == BID].size AS "bidSize"
TYPE "TradeAndBBO"
FROM kraken
ARRAY JOIN (entries AS array(L1entry))[THIS IS NOT NULL] AS bbo
Casting to a Common Type
You can take messages of two different types, lets say type "TradeMessage" and type "BestBidOfferMessage" and UNION them under one common message type for example type "NewType". As the result of such a query, you will get a fixed type dataset with fields from both classes combined in one new class NewType.
Stream UNION
caution
This feature is available starting from QQL 5.6.
If you want to perform the same action on different streams, you can use UNION in a single query. This way, you can have one message source subscribed to several streams.
REVERSE and LIVE queries are also supported with UNIONS. Refer to Queries to learn more.
Restrictions
- Message types (classes) with identical names in streams the UNION applies to, must be binary identical (have identical number of fields, names, types, and order).
- Union streams must be enclosed in brackets:
select * from (stream1 union stream2).
# Aggregation of bars from three streams
with this.entries[this is L1Entry][0] as trade
select
trade.exchangeId as exhcnage,
max{}(trade.price) as high,
min{}(trade.price) as low,
first{}(trade.price) as open,
last{}(trade.price) as close
from (binance UNION kraken UNION bitfinex)
over time(5s)
where trade != null and symbol == 'BTC/USD'